/* 
	Table 5: Population Estimates and Counterfactual Scenarios of of Linkages between HLG and Logged Home Value by 1960
	Do file to create population estimates table for true black and white sample as well
	as counterfacruals.
	
	Chinyere O Agbai 

	*/
	
global dir = "YourFilePath"
global output = "$dir"
global working_data = "$dir/ipums60_pub.dta"

cd "$output"
use "$working_data", clear

* External Constants 
cap gen prown_white = 0.74 //predicted probability of homeownership for White veterans (fig4)
cap gen prown_black = 0.43 //predicted probability of homeownership for Black veterans (fig4)
cap gen prval_white = 108848 //predicted home value for White veterans (fig5)
cap gen prval_black = 65081 //predicted home valuefor Black veterans (fig5)
			
cap gen cf2_prown_black = 0.43 //cf2 predicted probability of homeownership for black veterans 
cap gen cf2_prval_black = 70183 //cf2 predicted home value for black veterans 
			
**********************************************************************************************************
* Table 5: Population Estimates **************************************************************************
**********************************************************************************************************
scalar drop _all
	
putexcel set T5_pop_estimates.xls, replace //replace
putexcel A1 = ("Table 5: Population Estimates of Linkages between HLG and Home Value") ///
	B2 = ("Black") C2 = ("White") D2 = ("CF 1: Equal Veteran Share") ///
	E2 = ("CF 2: Equal SES") F2 = ("CF 3: Equal Impact on Ownership") ///
	G2 = ("CF 4: Equal Impact on Home Value") ///
	H2 = ("CF 5: CF 1-4 Combined") ///
	A3 = ("Total Men Born 1912-1932") ///
	A4 = ("Total WWII Veterans") A5 = ("%Veterans") ///
	A6 = ("Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans)") ///
	A7 = ("% Men Made Homeowners by HLG") A8 = ("Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)") ///
	A9 = ("Total Home Value in Millions (2018$)") ///
	A10 = ("Home Value Per Capita") ///
	A11 = ("Home Value Per Veteran") ///
	A12 = ("Home Value Per Veteran by 2023 at 3% Annual Increase") 

local row = 3
		
* Total men by race in sample	
forvalues i = 1/2 {
	count if race == `i' & yob >=1912 & yob <= 1932   
	if (`i' == 1) {
		scalar count_white = r(N) 
		local count_white: di %9.0gc r(N)
		di `count_white'
		continue
		}
				
	if (`i' == 2) {
		scalar count_black = r(N)
		local count_black : di %9.0gc r(N) 
		di `count_black'
		continue
		}
		}
	putexcel B`row' =("`count_black'") 
	putexcel C`row' =("`count_white'") 
	putexcel D`row' =("`count_black'") 
	putexcel E`row' =("`count_black'") 
	putexcel F`row' =("`count_black'") 
	putexcel G`row' =("`count_black'") 
	putexcel H`row' =("`count_black'") 

	local row = `row' + 1
			
* Total WWII Vets by race 
forvalues i = 1/2 {	
	count if race == `i' & vetbin_60 == 1 
		if (`i' == 1) {
			local totvet_white: di %8.0gc r(N)
			di `totvet_white'
			continue
			}
		if (`i' == 2) {
			local totvet_black : di %8.0gc r(N) 
			di `totvet_black'
			continue
			}
			}
	local row = 4		
	putexcel B`row' = ("`totvet_black'") 
	putexcel C`row' = ("`totvet_white'") 

	putexcel E`row' = ("`totvet_black'") 
	putexcel F`row' = ("`totvet_black'") 
	putexcel G`row' = ("`totvet_black'") 
	local row = `row' + 1

		
* % WWII Vets by race 
count if race == 2 & yob >=1912 & yob <= 1932   
scalar count_black = r(N) 			

count if race == 1 & yob >=1912 & yob <= 1932   
scalar count_white = r(N) 
di " count_black and count_white"
		
	*recreate the count of vets 
	count if race == 2 & vetbin_60 == 1
	scalar totvet_black = r(N) 
	
	count if race == 1 & vetbin_60 == 1
	scalar totvet_white = r(N)
	di "totvet_white & totvet_black"
			
* % of vets by race
scalar pvet_black = 100 * (totvet_black / count_black)	
local pvet_black: di %4.2f  pvet_black 

scalar pvet_white = 100 * (totvet_white / count_white)	
local pvet_white: di %4.2f  pvet_white 
			
local row = 5 
putexcel B`row' = ("`pvet_black'") 
putexcel C`row' = ("`pvet_white'") 		
putexcel D`row' = ("`pvet_white'") 
putexcel E`row' = ("`pvet_black'") 
putexcel F`row' = ("`pvet_black'") 
putexcel G`row' = ("`pvet_black'") 
putexcel H`row' = ("`pvet_white'") 

local row = `row' + 1
			
* Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans)  predictions from figuers 
scalar totown_black = prown_black * totvet_black 
local totown_black: di %7.0fc totown_black 
			
scalar totown_white = prown_white * totvet_white
local totown_white: di %7.0fc totown_white
			
putexcel B`row' = ("`totown_black'")
putexcel C`row' = ("`totown_white'")
local row = `row' + 1
			
* % Men Made Homeowners by HLG
scalar pown_black = 100*(totown_black / count_black)			
local pown_black: di %4.2f pown_black

scalar pown_white = 100*(totown_white / count_white)			
local pown_white: di %4.2f pown_white
			
di "`pown_black' and `pown_white'"

putexcel B`row' = ("`pown_black'")
putexcel C`row' = ("`pown_white'")
local row = `row' + 1
		
* Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners) 
scalar homeval_black = prval_black * totown_black
local homeval_black: di %12.0fc homeval_black
			
scalar homeval_white = prval_white * totown_white
local homeval_white: di %12.0fc homeval_white
	
putexcel B`row' = ("`homeval_black'")
putexcel C`row' = ("`homeval_white'")
local row = `row' + 1
		
* Total Housing Wealth in Millions (2018$) 
local homeval_black_mil: di %4.0fc homeval_black/1000000
local homeval_white_mil: di %4.0fc homeval_white/1000000
			
putexcel B`row' = ("`homeval_black_mil'")
putexcel C`row' = ("`homeval_white_mil'")
local row = `row' + 1
		
* Home val per capita (total housing wealth/#total men)
scalar percap_homeval_black = homeval_black/count_black	
scalar percap_homeval_white = homeval_white/count_white
		
local percap_homeval_black: di %7.0fc percap_homeval_black		
local percap_homeval_white: di %7.0fc percap_homeval_white
			
di " percap_homeval_black & percap_homeval_white"
			
//	local row = 10
putexcel B`row' = ("`percap_homeval_black'")
putexcel C`row' = ("`percap_homeval_white'")
			
local row = `row' + 1

* Housing wealth per veteran (total housing wealth/#total vets)	
local row = 11 

scalar vet_percap_homeval_black = homeval_black/totvet_black			
scalar vet_percap_homeval_white = homeval_white/totvet_white
			
local vet_percap_homeval_black: di %7.0fc vet_percap_homeval_black		
local vet_percap_homeval_white: di %7.0fc vet_percap_homeval_white
			
di "`vet_percap_homeval_black' & `vet_percap_homeval_white'"
			
local row = 11 
putexcel B`row' = ("`vet_percap_homeval_black'")
putexcel C`row' = ("`vet_percap_homeval_white'")		
		
* COUNTERFACTUALS **************************************************************
	* CF 1: Blacks and whites vet share equal, Ceteris paribus *****************
		scalar cf1_totvet_black = (pvet_white/100) * count_black 
		scalar cf5_totvet_black = round(cf1_totvet_black) 
		local cf1_totvet_black :  di %9.0fc cf1_totvet_black
		di `cf1_totvet_black'
		putexcel D4 = ("`cf1_totvet_black'") 
		putexcel H4 = ("`cf1_totvet_black'") 
		
		* CF 1: Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans) when black vet share = white vet share 
		scalar cf1_totown_black = prown_black * cf1_totvet_black
		local cf1_totown_black: di %6.0fc cf1_totown_black
		
		local row = 6
		putexcel D`row' = ("`cf1_totown_black'") 
		
		local row = `row' + 1
				
		* CF1 % Men made homeowners by HLG 
		scalar cf1_pown_black = 100 * (cf1_totown_black/count_black)
		local cf1_pown_black : di %4.2f cf1_pown_black
		putexcel D`row' = ("`cf1_pown_black'") 
		local row = `row' + 1
		
		* CF1: Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)
		scalar cf1_homeval_black = prval_black * cf1_totown_black
		local cf1_homeval_black: di %9.0fc cf1_homeval_black
		di `cf1_homeval_black'
		putexcel D`row' = ("`cf1_homeval_black'") 
		local row = `row' + 1
	
		* CF1 Total Predicted Home Value in millions 
		local cf1_homeval_black_mil: di %4.0fc cf1_homeval_black/1000000
		di `cf1_homeval_black_mil'
		putexcel D`row' = ("`cf1_homeval_black_mil'")
		local row = `row' + 1

		* CF1: Housing wealth per capita (total housing wealth/total pop )
		local cf1_percap_homeval_black: di %7.0fc cf1_homeval_black/count_black			
		putexcel D`row' = ("`cf1_percap_homeval_black'") 
		local row = `row' + 1
		
		di "`cf1_homeval_black' & count_black"
		
		forvalues i = 2/4 {
			scalar cf`i'_totvet_black = totvet_black
			}
	
	* CF 2: Blacks have same distribution (sd and mean) logged income, education, and employment status , as whites 
		* CF 2: Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans) 
		scalar cf2_totown_black = cf2_prown_black * cf2_totvet_black
		local cf2_totown_black: di %6.0fc cf2_totown_black
		
		local row = 6
		putexcel E`row' = ("`cf2_totown_black'")  
		local row = `row' + 1
				
		* CF2 % Men made homeowners by HLG 
		scalar cf2_pown_black = 100 * (cf2_totown_black/count_black)
		local cf2_pown_black : di %4.2f cf2_pown_black
		putexcel E`row' = ("`cf2_pown_black'") 
		local row = `row' + 1
		
		* CF2: Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)
		scalar cf2_homeval_black = cf2_prval_black * cf2_totown_black 
		local cf2_homeval_black: di %9.0fc cf2_homeval_black
		di `cf2_homeval_black'
		putexcel E`row' = ("`cf2_homeval_black'") 
		local row = `row' + 1
	
		* CF2 Total Predicted Home Value in millions 
		local cf2_homeval_black_mil: di %4.0fc cf2_homeval_black/1000000
		di `cf2_homeval_black_mil'
		putexcel E`row' = ("`cf2_homeval_black_mil'")
		local row = `row' + 1

		* CF2 Housing wealth per capita (total housing wealth/total pop)
		local cf2_percap_homeval_black: di %7.0fc cf2_homeval_black/count_black			
		putexcel E`row' = ("`cf2_percap_homeval_black'") 
		local row = `row' + 1
		
		di "`cf2_homeval_black' & count_black"
		
		
	* CF 3: Equal HLG impact on Ownership **************************************
		* CF3 : Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans) When hlg ownership effect for black == white 
			local row = 6
			scalar cf3_totown_black = prown_white * totvet_black 
			local cf3_totown_black: di %7.0fc cf3_totown_black 
			di `cf3_totown_black'
			putexcel F`row' = ("`cf3_totown_black'")
			local row = `row' + 1
			
		* CF3: % Men Made Homeowners by HLG
			scalar cf3_pown_black = 100*(cf3_totown_black / count_black)			
			local cf3_pown_black: di %4.2f cf3_pown_black
			di `cf3_pown_black'
			putexcel F`row' = ("`cf3_pown_black'")
			local row = `row' + 1
		
		* CF3: Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)
			scalar cf3_homeval_black = prval_black * cf3_totown_black
			local cf3_homeval_black: di %10.0fc cf3_homeval_black
			di `cf3_homeval_black'
			putexcel F`row' = ("`cf3_homeval_black'")
			local row = `row' + 1
		
		* CF3 Total housing wealth in millions 	
			local cf3_homeval_black_mil: di %4.0fc cf3_homeval_black/1000000
			di `cf3_homeval_black_mil'
			putexcel F`row' = ("`cf3_homeval_black_mil'")
			local row = `row' + 1
		
		* CF3: Housing wealth per capita (total housing wealth/total pop because #vets keeps chaging. we want consistent denom across cf)
			local cf3_percap_homeval_black: di %7.0fc cf3_homeval_black/count_black			
			putexcel F`row' = ("`cf3_percap_homeval_black'") 
			local row = `row' + 1
			
			di "`cf3_homeval_black' & count_black"
	
	* CF 4: Equal HLG impact on Home Value *************************************
		* CF4 : Total Homeowners (Pred. prob(homeowner) * Total WWII Veterans)
			local row = 6
			scalar cf4_totown_black = prown_black * totvet_black
			local cf4_totown_black: di %7.0fc cf4_totown_black 
			di `cf4_totown_black'
			putexcel G`row' = ("`cf4_totown_black'")
			local row = `row' + 1
			
		* CF4: % Men Made Homeowners by HLG
			scalar cf4_pown_black = 100*(cf4_totown_black / count_black)			
			local cf4_pown_black: di %4.2f cf4_pown_black
			di `cf4_pown_black'
			putexcel G`row' = ("`cf4_pown_black'")
			local row = `row' + 1
		
		* CF4: Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)  When hlg home val effect for blacks == white effect
			scalar cf4_homeval_black = prval_white * cf4_totown_black
			local cf4_homeval_black: di %10.0fc cf4_homeval_black
			di `cf4_homeval_black'
			putexcel G`row' = ("`cf4_homeval_black'")
			local row = `row' + 1
		
		* CF4 Total housing wealth in millions 	
			local cf4_homeval_black_mil: di %4.0fc cf4_homeval_black/1000000
			di `cf4_homeval_black_mil'
			putexcel G`row' = ("`cf4_homeval_black_mil'")
			local row = `row' + 1
		
		* CF4: Housing wealth per capita (total housing wealth/total pop)
			local cf4_percap_homeval_black: di %7.0fc cf4_homeval_black/count_black			
			di "`cf4_percap_homeval_black' & count_black"

			putexcel G`row' = ("`cf4_percap_homeval_black'") 
			local row = `row' + 1
		
			
		* CF5: All counterfactuals combined col h 
			* CF5 Total black homeowenrs (cf1 white vet share * white effects of hLG on ownership)
			scalar cf5_totown_black = prown_white * round(cf5_totvet_black) 
			local cf5_totown_black: di %6.0fc cf5_totown_black
			di cf5_totown_black
			
			local row = 6
			putexcel H`row' = ("`cf5_totown_black'") 
			local row = `row' + 1
			
			* CF5:  % Men Made Homeowners by HLG
			scalar cf5_pown_black = 100*(cf5_totown_black / count_black)			
			local cf5_pown_black: di %4.2f cf5_pown_black
			di `cf5_pown_black'
			putexcel H`row' = ("`cf5_pown_black'")
			local row = `row' + 1
					
		* CF5: Total Predicted Home Value (Pred. Home Value ($2018)* Total Homeowners)
			scalar cf5_homeval_black = prval_white * round(cf5_totown_black)
			local cf5_homeval_black: di %10.0fc cf5_homeval_black
			di `cf5_homeval_black'
			putexcel H`row' = ("`cf5_homeval_black'")
			local row = `row' + 1
		
		* CF5 Total housing wealth in millions 	
			local cf5_homeval_black_mil: di %4.0fc cf5_homeval_black/1000000
			di `cf5_homeval_black_mil'
			putexcel H`row' = ("`cf5_homeval_black_mil'")
			local row = `row' + 1
		
		* CF5: Housing wealth per capita (total housing wealth/total pop)
			local cf5_percap_homeval_black: di %7.0fc cf5_homeval_black/count_black			
			di "`cf5_percap_homeval_black' & count_black"

			putexcel H`row' = ("`cf5_percap_homeval_black'") 
			local row = `row' + 1
	
		* ALL COLUMNS : home value per veteran (total home value /#total vets)
			local row = 11 
			forvalues i = 1/5 {  
				scalar cf`i'_percap_vet = cf`i'_homeval_black/cf`i'_totvet_black 
				local cf`i'_percap_vet: di %6.0fc cf`i'_percap_vet
					
				di `cf`i'_percap_vet'
			
				if (`i' == 1) {
					putexcel D`row' = ("`cf`i'_percap_vet'")
					}
				else if (`i' == 2) {
					putexcel E`row' = ("`cf`i'_percap_vet'")
					}
				else if (`i' == 3) {
					putexcel F`row' = ("`cf`i'_percap_vet'")
					}
				else if (`i' == 4) {
					putexcel G`row' = ("`cf`i'_percap_vet'")
					}
				else if (`i' == 5) {
					putexcel H`row' = ("`cf`i'_percap_vet'")
					}			
					}
				local row = `row' + 1

		* ALL COLUMNS : home value per veteran in 2023 dollars at 3% increase / yr  (total housing wealth/#total vets)
			/*Compound interest formula: P = e ^rt, where P = principal , total home value in 2023 
			r = rate, and t = number of periods elapsed 1960 to 2023 (63) */
			
			local row = 12 
			* True black and white samples to 2023 
			foreach i in vet_percap_homeval_black vet_percap_homeval_white {
				scalar `i'_23 = `i' * ((1+0.03)^63)
				local `i'_23: di %7.0fc `i'_23 
				
				di ``i'_23'
				
				if (`i' == vet_percap_homeval_black) {
					putexcel B`row' = ("``i'_23'")
					}
				if (`i' == vet_percap_homeval_white) {
					putexcel C`row' = ("``i'_23'")
					}
				}
			* counterfactuals 
			forvalues i = 1/5 {  
				scalar cf`i'_percap_vet_23 = cf`i'_percap_vet * ((1+0.03)^63) 
				local cf`i'_percap_vet_23 : di %7.0fc cf`i'_percap_vet_23
				
				if (`i' == 1) {
					putexcel D`row' = ("`cf`i'_percap_vet_23'")
					}
				else if (`i' == 2) {
					putexcel E`row' = ("`cf`i'_percap_vet_23'")
					}
				else if (`i' == 3) {
					putexcel F`row' = ("`cf`i'_percap_vet_23'")
					}
				else if (`i' == 4) {
					putexcel G`row' = ("`cf`i'_percap_vet_23'")
					}
				else if (`i' == 5) {
					putexcel H`row' = ("`cf`i'_percap_vet_23'")
					}
					}
			

			
			
	
